#!bin/bash

#定义变量：数据同步日期，标识同步哪一天日志数据
#第1，执行shell脚本时，传递参数
#第2，如果没有传递参数，同步前一天数据
if [ -n "$2" ] ; then
  data_date=$2
else
  data_date=`date -d "-1 days" +%F`
fi

#加载数据语句
DIM_MD_AREAS_SQL="
DROP FUNCTION IF EXISTS convert_geohash ;
CREATE FUNCTION convert_geohash AS 'net.bwie.jtp.app.udf.ConvertGeoHash'
      USING JAR 'hdfs://node101:8020/warehouse/app_jars/jtp-log-warehouse-1.0-SNAPSHOT.jar';
WITH
    tmp_area AS (
        SELECT
            ID, AREANAME, PARENTID, SHORTNAME, LEVEL, FLAG, WGS84_LNG, WGS84_LAT, GCJ02_LNG, GCJ02_LAT, BD09_LNG, BD09_LAT, dt
        FROM jtp_app_ods.ods_md_areas
        WHERE ${data_date}
)
INSERT OVERWRITE TABLE jtp_app_dim.dim_md_areas PARTITION (dt='${data_date}')
SELECT
    tt3.ID,t3.province,t3.city,t3.district,tt3.street
    ,tt3.BD09_LNG,tt3.BD09_LAT
    ,convert_geohash(BD09_LAT, BD09_LNG, 8) AS geo_hash
FROM (
         SELECT
             tt2.ID,t2.province,t2.city,tt2.district
         FROM (
                  SELECT
                      tt1.ID,t1.province,tt1.city
                  FROM (
                           SELECT
                               ID,AREANAME AS province
                           FROM jtp_app_ods.ods_md_areas
                           WHERE dt='2024-11-18' AND LEVEL=1
                       ) t1
                           LEFT JOIN (
                      SELECT
                          ID,PARENTID,AREANAME AS city
                      FROM jtp_app_ods.ods_md_areas
                      WHERE dt='2024-11-18' AND LEVEL=2
                  ) tt1 ON t1.ID=tt1.PARENTID
              )t2
                  LEFT JOIN (
             SELECT
                 ID,AREANAME AS district ,PARENTID
             FROM jtp_app_ods.ods_md_areas
             WHERE dt='2024-11-18' AND LEVEL=3
         ) tt2 ON t2.ID=tt2.PARENTID
     ) t3
LEFT JOIN (
    SELECT
        ID,AREANAME AS street ,PARENTID,BD09_LAT,BD09_LNG
    FROM jtp_app_ods.ods_md_areas
    WHERE dt='2024-11-18' AND LEVEL=4
)tt3 ON t3.ID=tt3.PARENTID
WHERE tt3.BD09_LAT IS NOT NULL AND tt3.BD09_LNG IS NOT NULL ;
"
#执行SQL语句
    /opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"
